package com.kj.tms.common.utils.excel;

import com.kj.tms.common.utils.StringUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;
import java.util.Map;

/**
 * @ProjectName: etms-1.0.0
 * @Package: com.hongtu.tms.commonutil.excel
 * @ClassName: ExportExcelUtil
 * @Description: [导出excel公共类]
 * @Author: wangmeng
 * @CreateDate: 2018/7/16 10:50
 * @UpdateUser: []
 * @UpdateDate: 2018/7/16 10:50
 * @UpdateRemark: []
 * @Version: 1.0
 * Copyright: 北京宏图天安 Copyright (c) 2018
 */
public class ExportExcelUtil {

    /**
     *
     * @param workbook
     * @param sheetNo
     * @param sheetName
     * @param zoneCount 分区编号从0开始
     * @param fSqlsMap
     * @param datasMap
     */
    public static HSSFWorkbook exportExcel(HSSFWorkbook workbook,int sheetNo,String sheetName,int zoneCount,Map fSqlsMap,Map datasMap,Map dataTypeMap){
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet();
        workbook.setSheetName(sheetNo, sheetName);
        //遍历分区
        int maxCol = 0;
        for (int i=0;i<zoneCount;i++){
            List fSqlList = (List) fSqlsMap.get(i);
            List dataList = (List) datasMap.get(i);
            int dataType = StringUtil.toInt(dataTypeMap.get(i));
            if (dataList == null && fSqlList != null) {//1.固定取数模式
                //遍历固定取数的数据，如标题、表头
                String fetchField = "";
                int cellType = 0;
                int startRow = 0;
                int mergeRow = 0;
                int startCol = 0;
                int mergeCol = 0;
                for (int j=0;j<fSqlList.size();j++) {
                    Map fSqlMap = (Map)fSqlList.get(j);
                    fetchField = StringUtil.toString(fSqlMap.get("FETCHFIELD"));
                    cellType = StringUtil.toInt(fSqlMap.get("CELLTYPE"));
                    startRow = StringUtil.toInt(fSqlMap.get("STARTROW"));
                    mergeRow = StringUtil.toInt(fSqlMap.get("MERGEROW"));
                    startCol = StringUtil.toInt(fSqlMap.get("STARTCOL"));
                    mergeCol = StringUtil.toInt(fSqlMap.get("MERGECOL"));
                    Row nrow = sheet.getRow(startRow);
                    if (nrow == null) {
                        nrow = sheet.createRow(startRow);
                    }
                    Cell cell = nrow.createCell(startCol);
                    cell.setCellValue(fetchField);
                    setStyle(dataType, cell, workbook, cellType);
                    if (mergeRow != 1 || mergeCol != 1) {//存在要合并的行或列
                        int firstRow = startRow;
                        int lastRow = startRow+mergeRow-1;
                        int firstCol = startCol;
                        int lastCol = startCol+mergeCol-1;
                        if (maxCol < lastCol) {
                            maxCol = lastCol;
                        }
                        mergeCell(workbook, sheet, nrow, firstRow, lastRow, mergeRow, firstCol, lastCol, mergeCol, dataType);
                    }
                }
            }else if(dataList != null && fSqlList != null) {//2.固定行取数模式,3.新增行模式
                //遍历新增行模式的数据列表
                for (int j=0;j<dataList.size();j++) {
                    Map dataMap = (Map)dataList.get(j);
                    //遍历取数字段列表
                    String fetchField = "";
                    int cellType =0;
                    int startRow = 0;
                    int mergeRow = 0;
                    int startCol = 0;
                    int mergeCol = 0;
                    for (int k=0;k<fSqlList.size();k++) {
                        Map fSqlMap = (Map)fSqlList.get(k);
                        fetchField = StringUtil.toString(fSqlMap.get("FETCHFIELD")).toUpperCase();
                        cellType = StringUtil.toInt(fSqlMap.get("CELLTYPE"));
                        startRow = StringUtil.toInt(fSqlMap.get("STARTROW"));
                        mergeRow = StringUtil.toInt(fSqlMap.get("MERGEROW"));
                        startCol = StringUtil.toInt(fSqlMap.get("STARTCOL"));
                        mergeCol = StringUtil.toInt(fSqlMap.get("MERGECOL"));
                        Row nrow = sheet.getRow(startRow+j);
                        if (nrow == null) {
                            nrow = sheet.createRow(startRow+j);
                        }
                        Cell cell = nrow.createCell(startCol);
                        cell.setCellValue(StringUtil.toString(dataMap.get(fetchField)));
                        setStyle(dataType, cell, workbook, cellType);
                        if (mergeRow != 1 || mergeCol != 1) {//存在要合并的行或列
                            int firstRow = startRow+j;
                            int lastRow = startRow+j+mergeRow-1;
                            int firstCol = startCol;
                            int lastCol = startCol+mergeCol-1;
                            if (maxCol < lastCol) {
                                maxCol = lastCol;
                            }
                            mergeCell(workbook, sheet, nrow, firstRow, lastRow, mergeRow, firstCol, lastCol, mergeCol, dataType);
                        }
                    }
                }
            }
        }
        //设置单元格宽度
        for (int i=0;i<=maxCol;i++) {
            sheet.setColumnWidth(i, 24*256);
        }
        return workbook;
    }

    /**
     * 合并单元格
     * @param workbook
     * @param sheet
     * @param row
     * @param firstRow
     * @param lastRow
     * @param mergeRow
     * @param firstCol
     * @param lastCol
     * @param mergeCol
     * @param dataType
     */
    public static void mergeCell(HSSFWorkbook workbook,HSSFSheet sheet,Row row,int firstRow,int lastRow,int mergeRow,int firstCol,int lastCol,int mergeCol,int dataType) {
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
        if (dataType == 3 || dataType == 4) {//表头和数据合并单元格加边框
            //当前行加边框
            for (int m=firstCol+1;m<firstCol+mergeCol;m++) {
                Cell nullCell = row.createCell(m);
                nullCell.setCellStyle(centerStyle(workbook));
            }
            //合并行加边框
            for (int m=firstRow+1;m<firstRow+mergeRow;m++) {
                Row nullrow = sheet.getRow(m);
                if (nullrow == null) {
                    nullrow = sheet.createRow(m);
                }
                for (int n=firstCol;n<firstCol+mergeCol;n++) {
                    Cell nullCell = nullrow.createCell(n);
                    nullCell.setCellStyle(centerStyle(workbook));
                }
            }
        }
    }

    /**
     * 设置单元格样式
     * @param dataType
     * @param cell
     * @param workbook
     * @param cellType
     */
    public static void setStyle(int dataType, Cell cell, HSSFWorkbook workbook,int cellType) {
        if (dataType == 1) {//标题
            cell.setCellStyle(titleCellStyle(workbook));
        }else if (dataType == 2) {//抬头
            cell.setCellStyle(leftNoBorderStyle(workbook));
        }else if (dataType == 3){//表头
            cell.setCellStyle(headCellStyle(workbook));
        }else {
            if (cellType == 2) {
                cell.setCellStyle(jeCellStyle(workbook));
            }else {
                cell.setCellStyle(leftStyle(workbook));
            }
        }
    }

    /**
     * 定义标题字体
     * @param workbook
     * @return
     */
    public static HSSFFont titleFont(HSSFWorkbook workbook) {
        HSSFFont titleFont = workbook.createFont();
        titleFont.setFontName("宋体");
        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
        titleFont.setFontHeightInPoints((short) 16);//字体大小
        return titleFont;
    }

    /**
     * 定义表头字体
     * @param workbook
     * @return
     */
    public static HSSFFont tbHeadFont(HSSFWorkbook workbook) {
        HSSFFont tbHeadFont = workbook.createFont();
        tbHeadFont.setFontName("宋体");
        tbHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
        tbHeadFont.setFontHeightInPoints((short) 11);//字体大小
        return tbHeadFont;
    }

    /**
     * 定义数据字体
     * @param workbook
     * @return
     */
    public static HSSFFont dataFont(HSSFWorkbook workbook) {
        HSSFFont dataFont = workbook.createFont();
        dataFont.setFontName("宋体");
        dataFont.setFontHeightInPoints((short)11);
        return dataFont;
    }

    /**
     * 左对齐样式
     * @param workbook
     * @return
     */
    public static CellStyle leftStyle(HSSFWorkbook workbook){
        CellStyle leftStyle = workbook.createCellStyle();
        leftStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        leftStyle.setAlignment(CellStyle.ALIGN_LEFT);
        leftStyle.setWrapText(true); //设置自动换行
        setBorder(leftStyle);
        leftStyle.setFont(dataFont(workbook));
        return leftStyle;
    }

    /**
     * 左对齐无边框样式
     * @param workbook
     * @return
     */
    public static CellStyle leftNoBorderStyle(HSSFWorkbook workbook){
        CellStyle leftStyle = workbook.createCellStyle();
        leftStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        leftStyle.setAlignment(CellStyle.ALIGN_LEFT);
        leftStyle.setFont(dataFont(workbook));
        return leftStyle;
    }

    /**
     * 居中样式
     * @param workbook
     * @return
     */
    public static CellStyle centerStyle(HSSFWorkbook workbook) {
        CellStyle centerStyle = workbook.createCellStyle();
        centerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        centerStyle.setAlignment(CellStyle.ALIGN_CENTER);
        centerStyle.setWrapText(true); //设置自动换行
        setBorder(centerStyle);
        centerStyle.setFont(dataFont(workbook));
        return centerStyle;
    }

    /**
     * 金额单元格的样式
     * @param workbook
     * @return
     */
    public static CellStyle jeCellStyle(HSSFWorkbook workbook) {
        CellStyle jeCellStyle = workbook.createCellStyle();
        jeCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直居中
        jeCellStyle.setAlignment(CellStyle.ALIGN_RIGHT); // 水平居右
        setBorder(jeCellStyle);
        HSSFDataFormat format = workbook.createDataFormat();
        jeCellStyle.setDataFormat(format.getFormat("_ * #,##0.00_ ;_ * \\-#,##0.00_ ;_ * \"-\"??_ ;_ @_ "));//会计格式
        jeCellStyle.setFont(dataFont(workbook));
        return jeCellStyle;
    }

    /**
     * 表格标题样式
     * @param workbook
     * @return
     */
    public static CellStyle titleCellStyle(HSSFWorkbook workbook) {
        CellStyle titleCellStyle = workbook.createCellStyle();
        titleCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直居中
        titleCellStyle.setAlignment(CellStyle.ALIGN_CENTER); // 水平居中
        titleCellStyle.setFont(titleFont(workbook));//选择需要用到的字体格式
        return titleCellStyle;
    }

    /**
     * 表头样式
     * @param workbook
     * @return
     */
    public static CellStyle headCellStyle(HSSFWorkbook workbook) {
        CellStyle headCellStyle = workbook.createCellStyle();
        headCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //垂直居中
        headCellStyle.setAlignment(CellStyle.ALIGN_CENTER); // 水平居中
        headCellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        headCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        setBorder(headCellStyle);
        headCellStyle.setFont(tbHeadFont(workbook)); //选择需要用到的字体格式
        return headCellStyle;
    }

    /**
     * 设置单元格的边框样式
     * @param style
     */
    public static void setBorder(CellStyle style){
        style.setBorderBottom(CellStyle.BORDER_THIN); //下边框
        style.setBorderLeft(CellStyle.BORDER_THIN);//左边框
        style.setBorderTop(CellStyle.BORDER_THIN);//上边框
        style.setBorderRight(CellStyle.BORDER_THIN);//右边框
    }

}
